﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

using System.Data;


namespace AlumniProject.Framework
{
    public class TaiKhoanAccess
    {
        readonly DBAccess _dba = new DBAccess();

        public DataTable getTaiKhoanByUserPass(string user, string pass)
        {
            int NParam = 2;

            String[] names = new String[NParam];
            names[0] = "@TenDangNhap";
            names[1] = "@MatKhau";

            Object[] values = new Object[NParam];
            values[0] = user;
            values[1] = pass;

            const string procName = "sp_GetTaiKhoanbyUser_Pass";


            return _dba.LayDuLieu(procName, names, values, NParam).Tables[0];

        }

        public TaiKhoan DangNhap(TaiKhoan tk)
        {
            var dba = new DBAccess();

            var taiKhoan = new TaiKhoan();

            SqlParameter[] parameter =
            {
                new SqlParameter("@tendn", SqlDbType.VarChar),
                new SqlParameter("@matkhau", SqlDbType.VarChar)
            };

            parameter[0].Value = tk.TenDangNhap;
            parameter[1].Value = tk.MatKhau;

            var table = dba.ExcuteQuery("TAIKHOAN_ADMIN_LOGIN_PROC", CommandType.StoredProcedure, parameter);
            if (table.Rows.Count == 1)
            {
                taiKhoan.TenDangNhap = table.Rows[0]["TenDangNhap"].ToString();
                DateTime ngayThamGia;
                int maCsv;
                DateTime.TryParse(table.Rows[0]["NgayThamGia"].ToString(), out ngayThamGia);
                int.TryParse(table.Rows[0]["MaCSV"].ToString(), out maCsv);
                taiKhoan.NgayThamGia = ngayThamGia;
                taiKhoan.MaCsv = maCsv;
                taiKhoan.IsAdmin = (bool)table.Rows[0]["IsAdmin"];
                taiKhoan.MaGv = table.Rows[0]["MaGV"].ToString();
                taiKhoan.MaDv = table.Rows[0]["MaDV"].ToString();
                taiKhoan.CapBac = table.Rows[0]["MaCapBac"].ToString();
                int trangThai;
                int.TryParse(table.Rows[0]["MaTT"].ToString(), out trangThai);
                taiKhoan.TrangThai = trangThai;
            }
            else if (table.Rows.Count < 1)
            {
                taiKhoan.TrangThai = -1;
                taiKhoan.Description = "Không tìm thấy tài khoản!";
            }
            else if (table.Rows.Count > 1)
            {
                taiKhoan.TrangThai = -1;
                taiKhoan.Description = "Tìm thấy quá nhiều tài khoản!";
            }
            return taiKhoan;
        }

        public TaiKhoan DangNhapSite(TaiKhoan tk)
        {
            var dba = new DBAccess();

            var taiKhoan = new TaiKhoan();

            SqlParameter[] parameter =
            {
                new SqlParameter("@tendn", SqlDbType.VarChar),
                new SqlParameter("@matkhau", SqlDbType.VarChar)
            };

            parameter[0].Value = tk.TenDangNhap;
            parameter[1].Value = tk.MatKhau;

            var table = dba.ExcuteQuery("TAIKHOAN_LOGIN_PROC", CommandType.StoredProcedure, parameter);
            if (table.Rows.Count == 1)
            {
                taiKhoan.TenDangNhap = table.Rows[0]["TenDangNhap"].ToString();
                DateTime ngayThamGia;
                int maCsv;
                DateTime.TryParse(table.Rows[0]["NgayThamGia"].ToString(), out ngayThamGia);
                int.TryParse(table.Rows[0]["MaCSV"].ToString(), out maCsv);
                taiKhoan.NgayThamGia = ngayThamGia;
                taiKhoan.MaCsv = maCsv;
                taiKhoan.IsAdmin = (bool)table.Rows[0]["IsAdmin"];
                taiKhoan.MaGv = table.Rows[0]["MaGV"].ToString();
                taiKhoan.MaDv = table.Rows[0]["MaDV"].ToString();
                taiKhoan.Name = table.Rows[0]["NAME"].ToString();
                taiKhoan.CapBac = table.Rows[0]["MaCapBac"].ToString();
                int trangThai;
                int.TryParse(table.Rows[0]["MaTT"].ToString(), out trangThai);
                taiKhoan.TrangThai = trangThai;
            }
            else if (table.Rows.Count < 1)
            {
                taiKhoan.TrangThai = -1;
                taiKhoan.Description = "Không tìm thấy tài khoản!";
            }
            else if (table.Rows.Count > 1)
            {
                taiKhoan.TrangThai = -1;
                taiKhoan.Description = "Tìm thấy quá nhiều tài khoản!";
            }
            return taiKhoan;
        }

        public DataTable TinhThanhFull()
        {
            var dba = new DBAccess();

            return dba.ExcuteQuery("TINH_LOAD_ALL", CommandType.StoredProcedure, null);
        }
        public DataTable LinhVucCTFull()
        {
            var dba = new DBAccess();

            return dba.ExcuteQuery("LINHVUC_CT_LOAD_PROC", CommandType.StoredProcedure, null);
        }
        public DataTable HocViFull()
        {
            var dba = new DBAccess();

            return dba.ExcuteQuery("HOCVI_LOADALL_PROC", CommandType.StoredProcedure, null);
        }

        public bool DangKyTaiKhoan(TaiKhoan tk, string type, GiangVien gv, Csv csv)
        {
            var res = false;
            DBAccess dba = new DBAccess();
            switch (type)
            {
                case "csv":
                    int mcsv = DangKyCuuSinhVien(csv);
                    if (mcsv>0)
                    {
                        SqlParameter[] parameters =
                        {
                            new SqlParameter("@TENDN",SqlDbType.VarChar),
                            new SqlParameter("@MATKHAU",SqlDbType.VarChar),
                            new SqlParameter("@EMAIL",SqlDbType.VarChar),

                            new SqlParameter("@MACVS",SqlDbType.VarChar),
                            new SqlParameter("@MAGV",SqlDbType.VarChar),
                            new SqlParameter("@MADV",SqlDbType.VarChar)
                        };

                        parameters[0].Value = tk.TenDangNhap;
                        parameters[1].Value = new CryptorEngine().Encrypt(tk.MatKhau, true);
                        parameters[2].Value = tk.Email;

                        csv.Macsv = mcsv;
                        parameters[3].Value = csv.Macsv;
                        parameters[4].Value = "";
                        parameters[5].Value = "";


                        res = dba.ExcuteNonQuery("TAIKHOAN_DANGKY_PROC", CommandType.StoredProcedure, parameters); ;
                    }
                    else
                    {
                        res = false;
                    }
                    break;
                case "gv":
                    if (DangKyGiangVien(gv))
                    {
                        SqlParameter[] parameters =
                        {
                            new SqlParameter("@TENDN",SqlDbType.VarChar),
                            new SqlParameter("@MATKHAU",SqlDbType.VarChar),
                            new SqlParameter("@EMAIL",SqlDbType.VarChar),

                            new SqlParameter("@MACVS",SqlDbType.VarChar),
                            new SqlParameter("@MAGV",SqlDbType.VarChar),
                            new SqlParameter("@MADV",SqlDbType.VarChar)
                        };

                        parameters[0].Value = tk.TenDangNhap;
                        parameters[1].Value = new CryptorEngine().Encrypt(tk.MatKhau, true);
                        parameters[2].Value = tk.Email;

                        parameters[3].Value = null;
                        parameters[4].Value = gv.Ma;
                        parameters[5].Value = null;


                        res = dba.ExcuteNonQuery("TAIKHOAN_DANGKY_PROC", CommandType.StoredProcedure, parameters); ;
                    }
                    else
                    {
                        res = false;
                    }
                    break;
            }


            return res;

        }

        private int DangKyCuuSinhVien(Csv csv)
        {
            SqlParameter[] parameters =
            {

                new SqlParameter("@HOTEN", SqlDbType.NVarChar),
                new SqlParameter("@CMND", SqlDbType.NVarChar),
                new SqlParameter("@GT", SqlDbType.Bit),

                new SqlParameter("@NGAYSINH", SqlDbType.SmallDateTime),
                new SqlParameter("@SONHA", SqlDbType.NVarChar),
                new SqlParameter("@MATINH", SqlDbType.VarChar),

                new SqlParameter("@SDT", SqlDbType.VarChar),
                new SqlParameter("@MALV_CT", SqlDbType.NVarChar),
                new SqlParameter("@NOICONGTAC", SqlDbType.NVarChar),
                //new SqlParameter("@MCSV", SqlDbType.Int)
            };
            parameters[0].Value = csv.Ten;
            parameters[1].Value = csv.Cmnd;
            parameters[2].Value = csv.GioiTinh;

            parameters[3].Value = csv.NgaySinh;
            parameters[4].Value = csv.SoNha;
            parameters[5].Value = csv.MaTinh;

            parameters[6].Value = csv.Sdt;
            parameters[7].Value = csv.MaLv;
            parameters[8].Value = csv.NoiCongTac;
            //parameters[9].Value = 0;

            //SqlParameter retval = parameters[9];
            //retval.Direction = ParameterDirection.ReturnValue;

            SqlCommand sqlcomm = new SqlCommand();
            sqlcomm.Parameters.AddRange(parameters);
            
            sqlcomm.CommandType = CommandType.StoredProcedure;
            sqlcomm.CommandText = "CUUSV_DANGKY_PROC";

      
            sqlcomm.Connection = new DBAccess().GetConnection();
            sqlcomm.Connection.Open();
            int kq=0;
            try
            {
                 kq = (int)sqlcomm.ExecuteScalar();//.ExecuteNonQuery(); // MISSING
            }
            catch (Exception e)
            {

            }
            finally
            {
                sqlcomm.Connection.Close();
            }
            //int retunvalue = (int)sqlcomm.Parameters["@MCSV"].Value;

            return kq;
            // return new DBAccess().ExcuteNonQuery("CUUSV_DANGKY_PROC", CommandType.StoredProcedure, parameters);
        }
        private bool DangKyGiangVien(GiangVien gv)
        {
            SqlParameter[] parameters =
            {
                new SqlParameter("@MAGV", SqlDbType.VarChar),
                new SqlParameter("@HOTENGV", SqlDbType.NVarChar),
                new SqlParameter("@GIOITINH", SqlDbType.Bit),
                new SqlParameter("@NGAYSINH", SqlDbType.SmallDateTime),
                new SqlParameter("@SDT", SqlDbType.VarChar),
                new SqlParameter("@MAHOCVI", SqlDbType.VarChar)

            };

            parameters[0].Value = gv.Ma;
            parameters[1].Value = gv.Ten;
            parameters[2].Value = gv.GioiTinh;
            parameters[3].Value = gv.NgaySinh;
            parameters[4].Value = gv.SDT;
            parameters[5].Value = gv.HocVi;

            return new DBAccess().ExcuteNonQuery("GIANGVIEN_ADD_PROC", CommandType.StoredProcedure, parameters);
        }

        DBAccess dbAccess = new DBAccess();
        public DataSet Load_DSTK()
        {
            return dbAccess.LayDuLieu("TK_LoadAllDS");
        }
    }

}